from flask import Flask, render_template, request, redirect, url_for, session, flash
import pymysql
from pymysql.cursors import DictCursor
import datetime

app = Flask(__name__)
app.secret_key = 'bank_system_secret_key'
app.config['DEBUG'] = True

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'jn050424',
    'db': 'kechengsheji',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}


# 获取数据库连接
def get_db_connection():
    return pymysql.connect(**DB_CONFIG)


# 首页
@app.route('/')
def index():
    if 'user_id' in session:
        if session['user_type'] == 'admin':
            return redirect(url_for('admin_dashboard'))
        else:
            return redirect(url_for('customer_dashboard'))
    return render_template('login.html')


# 登录（简化密码验证，直接使用明文）
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']  # 卡号
        password = request.form['password']
        user_type = request.form['user_type']

        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                if user_type == 'admins':
                    # 简化管理员登录（假设admin表存在，密码明文）
                    sql = "SELECT * FROM admins WHERE username = %s AND password = %s"
                    cursor.execute(sql, (username, password))
                    admin = cursor.fetchone()
                    if admin:
                        session['user_id'] = admin['admin_id']
                        session['user_type'] = 'admin'
                        return redirect(url_for('admin_dashboard'))
                    else:
                        flash('管理员账号或密码错误', 'error')
                else:
                    # 客户登录（直接查询明文密码）
                    sql = """
                    SELECT ui.*, ci.cardID, ci.balance, ci.pass
                    FROM userInfo ui
                    JOIN cardInfo ci ON ui.customerID = ci.customerID
                    WHERE ci.cardID = %s AND ci.pass = %s
                    """
                    cursor.execute(sql, (username, password))
                    customer = cursor.fetchone()
                    if customer:
                        session['user_id'] = customer['customerID']
                        session['username'] = customer['customerName']
                        session['user_type'] = 'customer'
                        session['card_id'] = customer['cardID']
                        return redirect(url_for('customer_dashboard'))
                    else:
                        flash('卡号或密码错误', 'error')
    return render_template('login.html')


# 管理员仪表盘
@app.route('/admin/dashboard')
def admin_dashboard():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT COUNT(*) as total FROM userInfo")
            customer_count = cursor.fetchone()['total']
            cursor.execute("SELECT COUNT(*) as total FROM cardInfo")
            card_count = cursor.fetchone()['total']
            cursor.execute("SELECT SUM(balance) as total FROM cardInfo")
            total_balance = cursor.fetchone()['total'] or 0
            cursor.execute("""
            SELECT ti.*, ci.cardID, ui.customerName, de.savingName
            FROM tradeInfo ti
            JOIN cardInfo ci ON ti.cardID = ci.cardID
            JOIN userInfo ui ON ci.customerID = ui.customerID
            JOIN deposit de ON ci.savingID = de.savingID
            ORDER BY ti.tradeDate DESC LIMIT 10
            """)
            transactions = cursor.fetchall()

    return render_template('admin/dashboard.html',
                           customer_count=customer_count,
                           card_count=card_count,
                           total_balance=total_balance,
                           transactions=transactions)


# 管理员：客户列表
@app.route('/admin/customers')
def admin_customers():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("""
            SELECT ui.*, ci.cardID, ci.balance, de.savingName
            FROM userInfo ui
            LEFT JOIN cardInfo ci ON ui.customerID = ci.customerID
            LEFT JOIN deposit de ON ci.savingID = de.savingID
            ORDER BY ui.customerID
            """)
            customers = cursor.fetchall()

    return render_template('admin/customers.html', customers=customers)


# 客户仪表盘
@app.route('/customer/dashboard')
def customer_dashboard():
    if session.get('user_type') != 'customer':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM userInfo WHERE customerID = %s", (session['user_id'],))
            customer = cursor.fetchone()
            cursor.execute("""
            SELECT ci.*, de.savingName
            FROM cardInfo ci
            JOIN deposit de ON ci.savingID = de.savingID
            WHERE ci.customerID = %s
            """, (session['user_id'],))
            cards = cursor.fetchall()
            cursor.execute("""
            SELECT * FROM tradeInfo 
            WHERE cardID = %s 
            ORDER BY tradeDate DESC LIMIT 10
            """, (session['card_id'],))
            transactions = cursor.fetchall()

    return render_template('customer/dashboard.html',
                           customer=customer,
                           cards=cards,
                           transactions=transactions)


# 客户：存款（简化事务处理）
@app.route('/customer/deposit', methods=['GET', 'POST'])
def customer_deposit():
    if session.get('user_type') != 'customer':
        return redirect(url_for('login'))

    if request.method == 'POST':
        card_id = request.form['card_id']
        amount = float(request.form['amount'])
        password = request.form['password']

        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                try:
                    # 验证密码（明文比较）
                    sql = "SELECT pass, balance FROM cardInfo WHERE cardID = %s"
                    cursor.execute(sql, (card_id,))
                    card = cursor.fetchone()
                    if not card or card['pass'] != password:
                        flash('密码错误', 'error')
                        return redirect(url_for('customer_deposit'))

                    # 直接更新余额（无事务）
                    new_balance = card['balance'] + amount
                    sql = "UPDATE cardInfo SET balance = %s WHERE cardID = %s"
                    cursor.execute(sql, (new_balance, card_id))

                    # 记录交易（无事务）
                    trade_id = f"T{datetime.datetime.now().strftime('%Y%m%d%H%M%S')}"
                    sql = """
                    INSERT INTO tradeInfo (tradeID, tradeDate, tradeType, cardID, tradeMoney, remark)
                    VALUES (%s, %s, '存款', %s, %s, '系统存款')
                    """
                    cursor.execute(sql, (trade_id, datetime.datetime.now(), card_id, amount))

                    conn.commit()  # 简化提交
                    flash(f'存款成功，当前余额: {new_balance}', 'success')
                    return redirect(url_for('customer_dashboard'))
                except Exception as e:
                    conn.rollback()
                    flash(f'存款失败: {str(e)}', 'error')

    return render_template('customer/deposit.html')


# 登出
@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))


# 404错误处理
@app.errorhandler(404)
def page_not_found(e):
    return render_template('404.html'), 404


# 主程序入口
if __name__ == '__main__':
    try:
        with get_db_connection() as conn:
            print("数据库连接成功")
    except Exception as e:
        print(f"数据库连接失败: {e}")
        exit(1)

    print("银行系统启动中...")
    print("访问地址: http://localhost:5000")
    app.run(host='0.0.0.0', port=5000)